﻿using Dapper;
using Entity;
using IRealize;
using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Realize
{
    public class SCGLD : ISCGL
    {
        public async Task<int> DeleteHF(int id, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@"  delete from [dbo].[human_file] where [huf_id]='{id}' ";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        public async Task<PagedResult<Human_File>> SHFSCXS(string sqlserver, string firstKindName, string secondKindName, string thirdKindName, string jobCategoryName, string jobName, DateTime? startDate, DateTime? endDate, int pageNumber = 1, int pageSize = 1)
        {
            // 使用OFFSET/FETCH实现分页
            string sql = @"
SELECT * 
FROM (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY human_id) AS RowNum
    FROM human_file hf
    WHERE 1=1
    AND (@FirstKindName IS NULL OR hf.first_kind_name = @FirstKindName)
    AND (@SecondKindName IS NULL OR hf.second_kind_name = @SecondKindName)
    AND (@ThirdKindName IS NULL OR hf.third_kind_name = @ThirdKindName)
    AND (@JobCategoryName IS NULL OR hf.human_major_kind_name = @JobCategoryName)
    AND (@JobName IS NULL OR hf.hunma_major_name = @JobName)
    AND (@StartDate IS NULL OR hf.regist_time >= @StartDate)
    AND (@EndDate IS NULL OR hf.check_time <= @EndDate)
    AND hf.check_status = 2 
 AND hf.human_file_status = 1
    
) AS SubQuery
WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY RowNum";

            string countSql = @"
SELECT COUNT(*)    
FROM human_file hf
WHERE 1=1
AND (@FirstKindName IS NULL OR hf.first_kind_name = @FirstKindName)
AND (@SecondKindName IS NULL OR hf.second_kind_name = @SecondKindName)
AND (@ThirdKindName IS NULL OR hf.third_kind_name = @ThirdKindName)
AND (@JobCategoryName IS NULL OR hf.human_major_kind_name = @JobCategoryName)
AND (@JobName IS NULL OR hf.hunma_major_name = @JobName)
AND (@StartDate IS NULL OR hf.regist_time >= @StartDate)
AND (@EndDate IS NULL OR hf.check_time <= @EndDate)
AND hf.check_status = 2
 AND hf.human_file_status = 1  
";

            using (var connection = new SqlConnection(sqlserver))
            {
                await connection.OpenAsync();

                var parameters = new DynamicParameters();
                //parameters.Add("@FirstKindName", firstKindName);
                //parameters.Add("@SecondKindName", secondKindName);
                //parameters.Add("@ThirdKindName", thirdKindName);
                //parameters.Add("@JobCategoryName", jobCategoryName);
                //parameters.Add("@JobName", jobName);

                parameters.Add("@FirstKindName", string.IsNullOrEmpty(firstKindName) ? null : firstKindName);
                parameters.Add("@SecondKindName", string.IsNullOrEmpty(secondKindName) ? null : secondKindName);
                parameters.Add("@ThirdKindName", string.IsNullOrEmpty(thirdKindName) ? null : thirdKindName);
                parameters.Add("@JobCategoryName", string.IsNullOrEmpty(jobCategoryName) ? null : jobCategoryName);
                parameters.Add("@JobName", string.IsNullOrEmpty(jobName) ? null : jobName);
                parameters.Add("@StartDate", startDate);
                parameters.Add("@EndDate", endDate);
                parameters.Add("@PageNumber", pageNumber);
                parameters.Add("@PageSize", pageSize);

                // 执行分页查询
                var items = await connection.QueryAsync<Human_File>(sql, parameters);

                // 获取总记录数
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                return new PagedResult<Human_File>
                {
                    Items = items.ToList(),
                    TotalCount = totalCount,
                    PageNumber = pageNumber,
                    PageSize = pageSize
                };
            }


        }

        public async Task<PagedResult<Human_File>> SHFSCXS1(string sqlserver, string firstKindName, string secondKindName, string thirdKindName, string jobCategoryName, string jobName, DateTime? startDate, DateTime? endDate, int pageNumber = 1, int pageSize = 1)
        {
            // 使用OFFSET/FETCH实现分页
            string sql = @"
SELECT * 
FROM (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY human_id) AS RowNum
    FROM human_file hf
    WHERE 1=1
    AND (@FirstKindName IS NULL OR hf.first_kind_name = @FirstKindName)
    AND (@SecondKindName IS NULL OR hf.second_kind_name = @SecondKindName)
    AND (@ThirdKindName IS NULL OR hf.third_kind_name = @ThirdKindName)
    AND (@JobCategoryName IS NULL OR hf.human_major_kind_name = @JobCategoryName)
    AND (@JobName IS NULL OR hf.hunma_major_name = @JobName)
    AND (@StartDate IS NULL OR hf.regist_time >= @StartDate)
    AND (@EndDate IS NULL OR hf.check_time <= @EndDate)
    AND hf.check_status = 2 
 AND hf.human_file_status = 0
    
) AS SubQuery
WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize
ORDER BY RowNum";

            string countSql = @"
SELECT COUNT(*)    
FROM human_file hf
WHERE 1=1
AND (@FirstKindName IS NULL OR hf.first_kind_name = @FirstKindName)
AND (@SecondKindName IS NULL OR hf.second_kind_name = @SecondKindName)
AND (@ThirdKindName IS NULL OR hf.third_kind_name = @ThirdKindName)
AND (@JobCategoryName IS NULL OR hf.human_major_kind_name = @JobCategoryName)
AND (@JobName IS NULL OR hf.hunma_major_name = @JobName)
AND (@StartDate IS NULL OR hf.regist_time >= @StartDate)
AND (@EndDate IS NULL OR hf.check_time <= @EndDate)
AND hf.check_status = 2
 AND hf.human_file_status = 0  
";

            using (var connection = new SqlConnection(sqlserver))
            {
                await connection.OpenAsync();

                var parameters = new DynamicParameters();
                //parameters.Add("@FirstKindName", firstKindName);
                //parameters.Add("@SecondKindName", secondKindName);
                //parameters.Add("@ThirdKindName", thirdKindName);
                //parameters.Add("@JobCategoryName", jobCategoryName);
                //parameters.Add("@JobName", jobName);



                parameters.Add("@FirstKindName", string.IsNullOrEmpty(firstKindName) ? null : firstKindName);
                parameters.Add("@SecondKindName", string.IsNullOrEmpty(secondKindName) ? null : secondKindName);
                parameters.Add("@ThirdKindName", string.IsNullOrEmpty(thirdKindName) ? null : thirdKindName);
                parameters.Add("@JobCategoryName", string.IsNullOrEmpty(jobCategoryName) ? null : jobCategoryName);
                parameters.Add("@JobName", string.IsNullOrEmpty(jobName) ? null : jobName);
                parameters.Add("@StartDate", startDate);
                parameters.Add("@EndDate", endDate);
                parameters.Add("@PageNumber", pageNumber);
                parameters.Add("@PageSize", pageSize);

                // 执行分页查询
                var items = await connection.QueryAsync<Human_File>(sql, parameters);

                // 获取总记录数
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                return new PagedResult<Human_File>
                {
                    Items = items.ToList(),
                    TotalCount = totalCount,
                    PageNumber = pageNumber,
                    PageSize = pageSize
                };
            }


        }

        public async Task<int> UpdateStatus(int id, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@"Update [dbo].[human_file] set [human_file_status]=0 where [huf_id]='{id}' ";
                return await sqlConnection.ExecuteAsync(sql);
            }

        }

        public async Task<int> UpdateStatusHF(int id, string sqlserver)
        {
            using (SqlConnection sqlConnection = new SqlConnection(sqlserver))
            {

                string sql = $@"Update [dbo].[human_file] set [human_file_status]=1 where [huf_id]='{id}' ";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

    }
}
